/*
 * $RCSfile: Sql6.java,v $
 * $Revision: 1.1 $
 * $Date: 2009-02-16 $
 *
 * Copyright (C) 2008 Skin, Inc. All rights reserved.
 *
 * This software is the proprietary information of Skin, Inc.
 * Use is subject to license terms.
 */
package com.skin.webcat.database.sql;

import java.io.BufferedReader;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.skin.webcat.util.Jdbc;

/**
 * <p>Title: Sql6</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * @author xuesong.net
 * @version 1.0
 */
public class Sql6 {
    private static final Logger logger = LoggerFactory.getLogger(Sql6.class);

    /**
     * @param connection
     * @param sql
     * @return SqlResult
     */
    public static SqlResult execute(Connection connection, String sql) {
        return execute(connection, new StringReader(sql));
    }

    /**
     * @param connection
     * @param reader
     * @return SqlResult
     */
    public static SqlResult execute(Connection connection, Reader reader) {
        int status = 200;
        StringBuilder buffer = new StringBuilder();
        StringBuilder result = new StringBuilder();
        Statement statement = null;

        try {
            String sql = null;
            boolean update = false;
            BufferedReader bufferedReader = SqlPlus.getBufferedReader(reader);

            while((sql = SqlPlus.getCommand(bufferedReader, buffer)) != null) {
                ResultSet resultSet = null;
                String action = SqlPlus.getAction(sql);

                if(action == null || action.length() < 1) {
                    continue;
                }

                /**
                 * 第一层检查
                 * 先处理可确定的情况, 其他数据库的特殊查询语句通过下面的statement.execute确定.
                 * 如果已经执行过update语句忽略所有的查询语句.
                 */
                if(update && (action.equals("select") || action.equals("show"))) {
                    continue;
                }

                if(statement == null) {
                    statement = connection.createStatement();
                }

                try {
                    logger.info("sql: {}", sql);

                    long t1 = System.currentTimeMillis();
                    boolean flag = statement.execute(sql);
                    long t2 = System.currentTimeMillis();

                    /**
                     * 第二层检查
                     * 如果已经执行过update语句忽略所有的查询语句.
                     */
                    if(update && flag) {
                        continue;
                    }

                    /**
                     * 如果第一个sql语句是查询语句, 那么执行查询并忽略其后的所有语句.
                     * 如果第一个不是查询语句, 那么忽略其后的所有查询语句.
                     */
                    if(flag) {
                        resultSet = statement.getResultSet();
                        SqlResult sqlResult = SqlPlus.getSqlResult(connection, resultSet);
                        sqlResult.setMessage("Time: " + (t2 - t1) + "ms, count: " + sqlResult.getCount());
                        return sqlResult;
                    }
                    else {
                        update = true;
                        int count = statement.getUpdateCount();
                        result.append("[SQL]: ");
                        result.append(sql);
                        result.append("\r\n");
                        result.append("Affected rows: ");
                        result.append(count);
                        result.append("\r\n");
                        result.append("Time: ");
                        result.append(t2 - t1);
                        result.append("ms\r\n\r\n");
                    }
                }
                catch(SQLException e) {
                    logger.error(e.getMessage(), e);
                    status = 500;
                    result.append("[SQL]: ");
                    result.append(sql);
                    result.append("\r\n\r\n");
                    result.append(e.getMessage());
                    result.append("\r\n\r\n");
                    Jdbc.rollback(connection);
                    break;
                }
                finally {
                    Jdbc.close(resultSet);
                }
            }
            Jdbc.commit(connection);
        }
        catch(Exception e) {
            logger.error(e.getMessage(), e);
            status = 500;
            result.append("[ERROR]: ");
            result.append(e.getMessage());
            result.append("\r\n\r\n");
            Jdbc.rollback(connection);
        }
        finally {
            SqlPlus.close(statement);
        }
        return new SqlResult(status, result.toString());
    }
}
